<?php

// /** Error reporting */
// error_reporting(E_ALL);
// ini_set('display_errors', TRUE);
// ini_set('display_startup_errors', TRUE);
// date_default_timezone_set('Europe/London');

// if (PHP_SAPI == 'cli')
// 	die('This example should only be run from a Web Browser');

// /** Include PHPExcel */
// require_once '../Classes/PHPExcel.php';


// $aData = array(
// 	array(
// 			'列1', '列2', '列3', '列4', '列5', '列6', '列7', '列8',
// 	),
// 	array(
// 			'1x', '2', '3', '4', '5', '6', '7', '8',
// 	),
// 	array(
// 			'21', '22', '23', '24', '25', '26', '27', '28',
// 	),
// 	array(
// 			'121', '122', '123', '124', '125', '126', '127', '128',
// 	),
// );

// //My_PhpExcelExportCommon::save2003($aData, 'd:/112121212.xls');
// My_PhpExcelExportCommon::output2007($aData);

/**
 * 通用 PhpExcel导出类  
 * 用于通过二维数组创建简单的excel 
 * 
 * @author Flan 20130302
 *
 */
Class My_PhpExcelExportCommon{

	/**
	 * 输出excel
	 * 
	 * @access public 
	 * @param array $aData 二维数组
	 * @return void
	 */
	static public function output($aData){
		self::output2003($aData);
	}

	/**
	 * 输出xlsx格式的excel
	 *
	 * @access public
	 * @param array $aData 二维数组
	 * @return void
	 */
	static public function output2007($aData){
		self::exports($aData, 'output', '2007');
	}
	
	/**
	 * 输出xls格式的excel
	 *
	 * @access public
	 * @param array $aData 二维数组
	 * @return void
	 */
	static public function output2003($aData){
		self::exports($aData, 'output', '2003');
	}
	
	
	/**
	 * 生成excel
	 *
	 * @access public
	 * @param array $aData 二维数组
	 * @param string $sSavePath 保存地址
	 * @return void
	 */
	static public function save($aData, $sSavePath){
		self::save2003($aData);
		//self::exports()
	}
	
	/**
	 * 生成xlsx格式的excel
	 *
	 * @access public
	 * @param array $aData 二维数组
	 * @param string $sSavePath 保存地址
	 * @return void
	 */
	static public function save2007($aData, $sSavePath=''){
		//self::excelSave($aData, '2007');
		self::exports($aData, 'save', '2007', $sSavePath);
	}
	
	/**
	 * 生成xls格式的excel
	 *
	 * @access public
	 * @param array $aData 二维数组
	 * @param string $sSavePath 保存地址
	 * @return void
	 */
	static public function save2003($aData, $sSavePath=''){
		//self::excelSave($aData);
		self::exports($aData, 'save', '2003', $sSavePath);
	}
	
	/**
	 * 导出excel
	 * 
	 * @access public 
	 * @param array $aData
	 * @param string $sType output/save
	 * @param string $sSavePath 保存地址
	 * @param string $sXlsType 2003/2007
	 * @return void
	 */
	static private function exports($aData=array(), $sType='output', $sXlsType='2003', $sSavePath=''){
	
		// get excel row name
		$aCoordinate = self::getCoordinate();
	
		// Create new PHPExcel object
		$objPHPExcel = new PHPExcel();
	
		// Set document properties
		$objPHPExcel->getProperties()
			->setCreator("Office")
			->setLastModifiedBy("Office")
			->setTitle("Office 2007 XLSX Document")
			->setSubject("Office 2007 XLSX Document")
			->setDescription("Office 2007 XLSX")
			->setKeywords("office 2007")
			->setCategory("Office");
	
		// Add some data
		$pSheet = $objPHPExcel->setActiveSheetIndex(0);
		if (empty($aData) || (!is_array($aData))){
			$pSheet->setCellValue('A1', 'no data！');
		} else {
			foreach ($aData as $nRow => $aD){
				foreach ($aD as $nCol => $s){
					$pSheet->setCellValue($aCoordinate[$nCol].($nRow + 1), $s);
				}
			}
		}
	
		// Rename worksheet
		$objPHPExcel->getActiveSheet()->setTitle('dataList');
	
		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);
		
		if ($sType == 'output'){ // output
			if ($sXlsType == '2003'){
				// Redirect output to a client’s web browser (Excel5)
				header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment;filename="'.time().'.xls"');
				header('Cache-Control: max-age=0');
					
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
				$objWriter->save('php://output');
				exit;
			} else {
				// output Excel2007
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
				header('Content-Disposition: attachment;filename="'.time().'.xlsx"');
				header('Cache-Control: max-age=0');
					
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
				$objWriter->save('php://output');
				exit;
			}
		} else { // save
			if ($sXlsType == '2003'){
				// Save Excel5 file
				$sSavePath = ($sSavePath == '') ? str_replace('.php', '.xls', __FILE__) : $sSavePath;
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
				$objWriter->save($sSavePath);
			} else {
				// Save Excel 2007 file
				$sSavePath = ($sSavePath == '') ? str_replace('.php', '.xlsx', __FILE__) : $sSavePath;
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
				$objWriter->save($sSavePath);
			}
		}
	}
	
	/**
	 * 生成excel的列名
	 * 
	 * @access public 
	 * @param int $nNum 列名组数 默认 10(10*26个) 
	 * @return array
	 */
	static private function getCoordinate($nNum=10){
		
		$aCoordinate = array();
		
		$a = 'A';
		for($i=1; $i<=26*$nNum; $i++){
			$aCoordinate[] = $a;
			$a++;
		}
		
		return $aCoordinate;
	}
}



